package com.example.demo4.dao;

import com.example.demo4.Bean.News;
import com.example.demo4.utils.DBConnection;
import com.example.demo4.utils.Time;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class NewsDao {
    DBConnection DBCoon = new DBConnection();
    //todo 完成增删改查功能
    public List<News> getList(int page) {
        Connection conn = DBCoon.getConn();
        String sql = "select * from news limit "+(page-1)*10+",10";
        List<News> list = new ArrayList<News>();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                News newsBean = new News();
                newsBean.setNewsid(rs.getInt("newsid"));
                newsBean.setNewstitle(rs.getString("newstitle"));
                newsBean.setNewscontenr(rs.getString("newscontent"));
                newsBean.setNewstime(Time.getDateTime());
                newsBean.setAdimname(rs.getString("adimnname"));
                list.add(newsBean);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    /**
     * @param news 传入要更新的新闻对象,必须包含新闻id值
     * @return 受影响的行数
     */
    public int updateNews(News news) {
        if (news.getNewsid() > 0) {
            Connection conn = DBCoon.getConn();
            try {
                String sql = "update news set newstitle=?,newscontent=?,adimnname=?,newstime=? where newsid=?";
                PreparedStatement pmst = conn.prepareStatement(sql);
                pmst.setString(1, news.getNewstitle());
                pmst.setString(2, news.getNewscontenr());
                pmst.setString(4, Time.getDateTime());
                pmst.setString(3, news.getAdimname());
                pmst.setInt(5, news.getNewsid());
                return pmst.executeUpdate();
            } catch (SQLException e){
                e.printStackTrace();
            }
            return 0;
        }
        return 0;
    }

    public int add(News news){
        Connection conn = DBCoon.getConn();
        try {
            String sql = "insert into news values(null,?,?,?,?)";
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setString(1,news.getNewstitle());
            pmst.setString(2,news.getNewscontenr());
            pmst.setString(3, (new Date()).toString() );
            pmst.setString(4,"临时管理员");
            System.out.println(pmst);
            return pmst.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return 0;
    }
    public int deleteNewsById(int newsid){
        Connection conn = DBCoon.getConn();
        try {
            String sql = "delete from news where newsid=?";
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setInt(1,newsid);
            return pmst.executeUpdate();
        }catch (SQLException e){
            System.out.println("deleteNewsById error");
            e.printStackTrace();
        }
        return 0;
    }

    public int countPage() {
        Connection conn = DBCoon.getConn();
        String sql = "select count(*) from news";
        try {
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);//获取查询结果
            if (rs.next()){
                return (rs.getInt(1)/10)+1;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return 0;
    }
}
